# -*- coding: utf-8 -*-
import xlrd,sqlite3

db="res2.db3"
xls="lan.xls"
conn=sqlite3.connect(db)
#conn.text_factory=str
conn.row_factory = sqlite3.Row
room=conn.execute("select id,room from rawroom").fetchall()

def getdistrictid(district):
    districtid=conn.execute("select id from rawdistrict where district=?",(district,)).fetchone()
    if districtid is None:
        conn.execute("insert into rawdistrict(district) values(?)",(district,))
        districtid=conn.execute("select last_insert_rowid()").fetchone()
    return districtid[0]

def getroomid(r):
    for x in room:
        if x['room']==r:
            return x['id']

def getsubroomid(sr):
    srid=conn.execute("select id from rawsubroom where subroom=? and district=? and room=? and signal=?",(sr[u'机房'],sr[u'districtid'],sr[u'roomid'],sr[u'信号'])).fetchone()
    if srid is None:
        conn.execute("insert into rawsubroom(subroom,subroomtype,subroomaddr,district,splitter,room,pon,signal) values(?,?,?,?,?,?,?,?)",(sr[u'机房'],sr[u'机房类型'],sr[u'机房地址'],sr[u'districtid'],sr[u'分光器'],sr[u'roomid'],sr[u'PON'],sr[u'信号']))
        srid=conn.execute("select last_insert_rowid()").fetchone()
    return srid[0]

def readodn():
    odn=readsheet('odn')
    for row in odn:
        row[u'districtid']=getdistrictid(row[u'小区名称'])
        row[u'roomid']=getroomid(row[u'分前端'])
        row[u'subroomid']=getsubroomid(row)
    return odn

def getresid(res):
    resid=conn.execute("select id from rawres where subroom=? and mac=? and installaddr=?",(res[u'subroomid'],res[u'MAC'],res[u'安装地址'])).fetchone()
    if resid is None:
        conn.execute("insert into rawres(ip,mac,installaddr,subroom,iptvvlan,opticalpower,managevlan,subnetmask,gateway,assetnumber,sn,memo,restype,device) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)",(res[u'IP'],res[u'MAC'],res[u'安装地址'],res[u'subroomid'],res[u'IPTV VLAN'],res[u'光功率'],res[u'管理VLAN'],res[u'子网掩码'],res[u'网关'],res[u'资产编号'],res[u'SN'],res[u'备注'],res[u'restype'],res[u'设备型号']))
        resid=conn.execute("select last_insert_rowid()").fetchone()
    else:
        conn.execute("update rawres set ip=?,mac=?,installaddr=?,subroom=?,iptvvlan=?,opticalpower=?,managevlan=?,subnetmask=?,gateway=?,assetnumber=?,sn=?,memo=?,restype=?,device=? where id=?",(res[u'IP'],res[u'MAC'],res[u'安装地址'],res[u'subroomid'],res[u'IPTV VLAN'],res[u'光功率'],res[u'管理VLAN'],res[u'子网掩码'],res[u'网关'],res[u'资产编号'],res[u'SN'],res[u'备注'],res[u'restype'],res[u'设备型号'],resid[0]))
    return resid[0]

def cleanrow(row):
    for i,e in enumerate(row):
        if type(e)==str:
            row[i]=e.strip()
            if e=='':
                row[i]=None
    return row

def cleancell(cell):
    if type(cell)==str:
        cell=cell.strip()
        if cell=='':
            cell=None
    return cell

def readsheet(sheet):
    sh = wb.sheet_by_name(sheet)
    nrows = sh.nrows
    ncols = sh.ncols
    headers=dict((i,sh.cell_value(0,i)) for i in range(ncols))
    return [dict( (headers[j], cleancell(sh.cell_value(i, j))) for j in headers ) for i in range(1, nrows)]


def cellupper(cell):
    if cell:
        return cell.upper()

def cellint(cell):
    if cell and type(cell)==float:
        return int(cell)

def addcoverage(res):
    coverage=res[u'覆盖范围'].split(';')
    for c in coverage:
        conn.execute("replace into rawaddr(addr,floor,door) values(?,?,?)",(c,None,None))
        conn.execute("replace into resaddr values(?,?)",(res[u'resid'],c))


def readonu(subroom):
    onu=readsheet('onu')
    for row in onu:

        row[u'IPTV VLAN']=cellint(row[u'IPTV VLAN'])
        row[u'管理VLAN']=cellint(row[u'管理VLAN'])
        row[u'MAC']=cellupper(row[u'MAC'])
        row[u'SN']=cellupper(row[u'SN'])
        row[u'资产编号']=cellupper(row[u'资产编号'])
        for s in subroom:
            if s[u'机房']==row[u'机房']:
                row[u'subroomid']=s[u'subroomid']
        row[u'restype']='LAN'
        row[u'resid']=getresid(row)
        addcoverage(row)
    print u'添加资源：',len(onu)

def readxls():
#    shxrange = range(wb.nsheets)
    subroom=readodn()
    readonu(subroom)



wb = xlrd.open_workbook(xls)

readxls()
conn.commit()
conn.close()
